<?php
if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class M_apotik extends CI_Model {

	public function __construct() {
		parent::__construct();
	}

	function get() {
		$sql = 'SELECT k.id_klaim_obat, k.kd_klaim klaim, p.kd_peserta AS peserta, 
		nama,tgl_transaksi,
		COALESCE( dko.CNT, 0 ) AS JML,
		COALESCE( dko.JUM, 0 ) AS BANYAK
		FROM klaim_obat k
		LEFT JOIN
			(SELECT detail_klaim_obat.id_klaim_obat, COUNT(*) AS CNT, SUM(frekuensi_diminta) AS JUM FROM detail_klaim_obat GROUP BY detail_klaim_obat.id_klaim_obat ) dko ON k.id_klaim_obat=dko.id_klaim_obat 
		LEFT JOIN klaim ON k.kd_klaim=klaim.kd_klaim
		LEFT JOIN surat_jaminan sj ON klaim.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik
		WHERE tgl_transaksi=DATE(NOW())';
		return $sql;
	}

	function get_cari_nama($keyword='') {
		$sql = 'SELECT k.id_klaim_obat, k.kd_klaim klaim, p.kd_peserta AS peserta, 
		nama,tgl_transaksi,
		COALESCE( dko.CNT, 0 ) AS JML,
		COALESCE( dko.JUM, 0 ) AS BANYAK
		FROM klaim_obat k
		LEFT JOIN
			(SELECT detail_klaim_obat.id_klaim_obat, COUNT(*) AS CNT, SUM(frekuensi_diminta) AS JUM FROM detail_klaim_obat GROUP BY detail_klaim_obat.id_klaim_obat ) dko ON k.id_klaim_obat=dko.id_klaim_obat 
		LEFT JOIN klaim ON k.kd_klaim=klaim.kd_klaim
		LEFT JOIN surat_jaminan sj ON klaim.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik
		WHERE nama like "%'.$keyword.'%" OR k.kd_klaim like "%'.$keyword.'%"
		ORDER BY nama,k.kd_klaim,tgl_transaksi';
		return $sql;
	}

	function get_cari_tgl($keyword='',$tgl='') {
		$sql = 'SELECT k.id_klaim_obat, k.kd_klaim klaim, p.kd_peserta AS peserta, 
		nama,tgl_transaksi,
		COALESCE( dko.CNT, 0 ) AS JML,
		COALESCE( dko.JUM, 0 ) AS BANYAK
		FROM klaim_obat k
		LEFT JOIN
			(SELECT detail_klaim_obat.id_klaim_obat, COUNT(*) AS CNT, SUM(frekuensi_diminta) AS JUM FROM detail_klaim_obat GROUP BY detail_klaim_obat.id_klaim_obat ) dko ON k.id_klaim_obat=dko.id_klaim_obat 
		LEFT JOIN klaim ON k.kd_klaim=klaim.kd_klaim
		LEFT JOIN surat_jaminan sj ON klaim.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik
		WHERE kd_ppk= '.$keyword.' AND tgl_transaksi="'.$tgl.'"
		ORDER BY nama,k.kd_klaim,tgl_transaksi';
		return $sql;
	}

	function get_cari_ppk($keyword='') {
		$sql = 'SELECT k.id_klaim_obat, k.kd_klaim klaim, p.kd_peserta AS peserta, 
		nama,tgl_transaksi,
		COALESCE( dko.CNT, 0 ) AS JML,
		COALESCE( dko.JUM, 0 ) AS BANYAK
		FROM klaim_obat k
		LEFT JOIN
			(SELECT detail_klaim_obat.id_klaim_obat, COUNT(*) AS CNT, SUM(frekuensi_diminta) AS JUM FROM detail_klaim_obat GROUP BY detail_klaim_obat.id_klaim_obat ) dko ON k.id_klaim_obat=dko.id_klaim_obat 
		LEFT JOIN klaim ON k.kd_klaim=klaim.kd_klaim
		LEFT JOIN surat_jaminan sj ON klaim.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik
		WHERE kd_ppk= '.$keyword.'
		ORDER BY nama,k.kd_klaim,tgl_transaksi';
		return $sql;
	}

	function get_cari_tgl_all($tgl='') {
		$sql = 'SELECT k.id_klaim_obat, k.kd_klaim klaim, p.kd_peserta AS peserta, 
		nama,tgl_transaksi,
		COALESCE( dko.CNT, 0 ) AS JML,
		COALESCE( dko.JUM, 0 ) AS BANYAK
		FROM klaim_obat k
		LEFT JOIN
			(SELECT detail_klaim_obat.id_klaim_obat, COUNT(*) AS CNT, SUM(frekuensi_diminta) AS JUM FROM detail_klaim_obat GROUP BY detail_klaim_obat.id_klaim_obat ) dko ON k.id_klaim_obat=dko.id_klaim_obat 
		LEFT JOIN klaim ON k.kd_klaim=klaim.kd_klaim
		LEFT JOIN surat_jaminan sj ON klaim.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik
		WHERE tgl_transaksi="'.$tgl.'"
		ORDER BY nama,k.kd_klaim,tgl_transaksi';
		return $sql;
	}

	function get_cari_ppk_all() {
		$sql = 'SELECT k.id_klaim_obat, k.kd_klaim klaim, p.kd_peserta AS peserta, 
		nama,tgl_transaksi,
		COALESCE( dko.CNT, 0 ) AS JML,
		COALESCE( dko.JUM, 0 ) AS BANYAK
		FROM klaim_obat k
		LEFT JOIN
			(SELECT detail_klaim_obat.id_klaim_obat, COUNT(*) AS CNT, SUM(frekuensi_diminta) AS JUM FROM detail_klaim_obat GROUP BY detail_klaim_obat.id_klaim_obat ) dko ON k.id_klaim_obat=dko.id_klaim_obat 
		LEFT JOIN klaim ON k.kd_klaim=klaim.kd_klaim
		LEFT JOIN surat_jaminan sj ON klaim.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik
		ORDER BY nama,k.kd_klaim,tgl_transaksi';
		return $sql;
	}

	function get_by($params) {
		$sql = 'SELECT id_klaim_obat, kd_klaim, tgl_transaksi,kd_dokter AS KDDOK,kd_unit AS KDUNIT,dmk
		FROM klaim_obat 
		WHERE id_klaim_obat=?';
		$query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_klaim($params) {
		$sql = 'SELECT kd_klaim, p.kd_peserta AS peserta, 
		nama,kl.alamat as ALAMAT,tempat_lahir,tgl_lahir,tgl_klaim,tgl_masuk,tgl_keluar,no_rm,ppk,
		first_name,last_name,ket_feedback1
		FROM klaim k
		LEFT JOIN rc_users u ON k.id_inputor=u.id
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN ppk  ON sj.kd_ppk=ppk.kd_ppk
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik
		LEFT JOIN keluarga kl ON pd.kd_kk=kl.kd_kk
		WHERE kd_klaim=?';
		$query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_apotik_by($params) {
		$sql = 'SELECT ko.kd_klaim AS klaim, p.kd_peserta AS peserta,dokter,unit,ko.dmk AS DMK, 
		nama,kl.alamat as ALAMAT,tempat_lahir,tgl_lahir,tgl_transaksi,ppk,no_rm,jk,DATEDIFF(tgl_keluar,tgl_masuk) AS lama,
		first_name,last_name,ket_feedback1,diagnosa_lain,jenis_kunjungan,tgl_masuk,tgl_keluar
		FROM klaim_obat ko
		LEFT JOIN dokter d ON ko.kd_dokter=d.kd_dokter
		LEFT JOIN unit un ON ko.kd_unit=un.kd_unit
		LEFT JOIN klaim k ON k.kd_klaim=ko.kd_klaim
		LEFT JOIN rc_users u ON k.id_inputor=u.id
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN ppk  ON sj.kd_ppk=ppk.kd_ppk
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik
		LEFT JOIN keluarga kl ON pd.kd_kk=kl.kd_kk
		WHERE id_klaim_obat=?';
		$query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_peserta($kd_peserta) {
		$sql = 'SELECT kd_klaim, p.kd_peserta AS peserta, 
		nama,alamat,tempat_lahir,tgl_lahir,kd_ppk
		FROM klaim k
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik
		LEFT JOIN keluarga kl ON pd.kd_kk=kl.kd_kk
		WHERE kd_klaim=?';
		$query = $this->db->query($sql,$kd_peserta);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	
	function get_apotik($keyword) {
		$sql = 'SELECT kd_obat,obat,merk,harga FROM klaim k
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN ppk ON sj.kd_ppk=ppk.kd_ppk
		LEFT JOIN obat o ON ppk.kd_ppk=o.kd_ppk
		WHERE k.kd_klaim=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_apotik_klaim($keyword) {
		$sql = 'SELECT id_detail_klaim_obat, dko.id_klaim_obat, obat,o.kd_obat AS kd_obat, merk, tarif_perda,tarif_diminta,
		frekuensi_diminta
		FROM detail_klaim_obat dko
		LEFT JOIN klaim_obat ko ON dko.id_klaim_obat=ko.id_klaim_obat
		LEFT JOIN obat o ON dko.kd_obat=o.kd_obat
		WHERE ko.id_klaim_obat=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_obat_by($keyword) {
		$sql = 'SELECT * FROM obat WHERE kd_obat=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_obat_klaim_by($keyword) {
		$sql = 'SELECT dko.kd_obat AS kode,obat,id_detail_klaim_obat,tarif_perda,tarif_diminta,frekuensi_diminta
		 FROM detail_klaim_obat dko 
		 LEFT JOIN obat o ON dko.kd_obat=o.kd_obat
		 WHERE id_detail_klaim_obat=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_export_apotik(){
		$sql = 'SELECT ko.kd_klaim AS klaim, p.kd_peserta AS peserta,id_klaim_obat,ko.dmk AS DMK,dokter,unit, 
		nama,kl.alamat as ALAMAT,tempat_lahir,tgl_lahir,tgl_transaksi,jk,first_name,last_name
		FROM klaim_obat ko
		LEFT JOIN unit un ON ko.kd_unit=un.kd_unit
		LEFT JOIN dokter d ON ko.kd_dokter=d.kd_dokter
		LEFT JOIN klaim k ON k.kd_klaim=ko.kd_klaim
		LEFT JOIN rc_users u ON k.id_inputor=u.id
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN ppk  ON sj.kd_ppk=ppk.kd_ppk
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik
		LEFT JOIN keluarga kl ON pd.kd_kk=kl.kd_kk';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_export_obat($keyword) {
		$sql = 'SELECT id_detail_klaim_obat, dko.id_klaim_obat, obat, merk, tarif_perda,tarif_diminta,
		frekuensi_diminta,kd_jenis_obat
		FROM detail_klaim_obat dko
		INNER JOIN klaim_obat ko ON dko.id_klaim_obat=ko.id_klaim_obat
		INNER JOIN obat o ON dko.kd_obat=o.kd_obat
		WHERE ko.id_klaim_obat=? AND o.kd_obat NOT IN("P","R","O2")';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_export_jasa($params) {
		$sql = 'SELECT tarif_diminta,frekuensi_diminta
		FROM detail_klaim_obat dko
		INNER JOIN klaim_obat ko ON dko.id_klaim_obat=ko.id_klaim_obat
		INNER JOIN obat o ON dko.kd_obat=o.kd_obat
		WHERE ko.id_klaim_obat=? AND o.kd_obat=?';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_export_jenisobat($params) {
		$sql = 'SELECT COUNT(*) AS tot
		FROM detail_klaim_obat dko
		INNER JOIN klaim_obat ko ON dko.id_klaim_obat=ko.id_klaim_obat
		INNER JOIN obat o ON dko.kd_obat=o.kd_obat
		WHERE ko.id_klaim_obat=? AND kd_jenis_obat=? AND o.kd_obat NOT IN("P","R","O2")';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function getPPK() {
		$sql = 'SELECT * FROM ppk WHERE id_jenis_ppk <> "1"';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	
	

	function get_ket($params) {
		$sql = 'SELECT ket FROM klaim WHERE id_agama = ? AND id_produksi = ?';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result['ket'];
	}

	function save($dataInsert) {
		$query = $this->db->insert('klaim_obat', $dataInsert);
		return $query;
	}

	function update($id, $dataUpdate) {
		$this->db->where('id_klaim_obat', $id);
		$update = $this->db->update('klaim_obat', $dataUpdate);
		return $update;
	}

	function delete($id_klaim_obat) {
		$this->db->where('id_klaim_obat', $id_klaim_obat);
		$delete = $this->db->delete('klaim_obat');
		return $delete;
	}

	function deleteObat($id_klaim_obat) {
		$this->db->where('id_klaim_obat', $id_klaim_obat);
		$delete = $this->db->delete('detail_klaim_obat');
		return $delete;
	}

	function cek($params){
		$sql="SELECT * FROM detail_klaim_obat WHERE kd_obat=? AND id_klaim_obat=?";
		$query=$this->db->query($sql,$params);
		return $query;
	}
	function save_obat($dataInsert) {
		$query = $this->db->insert('detail_klaim_obat', $dataInsert);
		return $query;
	}

	function update_obat($id, $dataUpdate) {
		$this->db->where('id_detail_klaim_obat', $id);
		$update = $this->db->update('detail_klaim_obat', $dataUpdate);
		return $update;
	}

	function delete_obat($id) {
		$this->db->where('id_detail_klaim_obat', $id);
		$delete = $this->db->delete('detail_klaim_obat');
		return $delete;
	}

	//dokter dan unit
	function get_dokter($keyword) {
		$sql = 'SELECT DISTINCT(d.kd_dokter), d.kd_dokter KDDOK,dokter FROM klaim_obat ko
		LEFT JOIN klaim k ON ko.kd_klaim=k.kd_klaim
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN ppk ON sj.kd_ppk=ppk.kd_ppk
		LEFT JOIN dokter d ON ppk.kd_ppk=d.kd_ppk
		WHERE ko.kd_klaim=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_dokter_edit($keyword) {
		$sql = 'SELECT d.kd_dokter KDDOK,dokter FROM klaim_obat ko
		LEFT JOIN klaim k ON ko.kd_klaim=k.kd_klaim
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN ppk ON sj.kd_ppk=ppk.kd_ppk
		LEFT JOIN dokter d ON ppk.kd_ppk=d.kd_ppk
		WHERE ko.id_klaim_obat=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_unit($keyword) {
		$sql = 'SELECT DISTINCT(u.kd_unit), u.kd_unit KDUNIT,unit FROM klaim_obat ko
		LEFT JOIN klaim k ON ko.kd_klaim=k.kd_klaim
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN ppk ON sj.kd_ppk=ppk.kd_ppk
		LEFT JOIN unit u ON ppk.kd_ppk=u.kd_ppk
		WHERE ko.kd_klaim=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_unit_edit($keyword) {
		$sql = 'SELECT u.kd_unit KDUNIT,unit FROM klaim_obat ko
		LEFT JOIN klaim k ON ko.kd_klaim=k.kd_klaim
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN ppk ON sj.kd_ppk=ppk.kd_ppk
		LEFT JOIN unit u ON ppk.kd_ppk=u.kd_ppk
		WHERE ko.id_klaim_obat=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}
	
}
